<!DOCTYPE html>
<!-- sheetjs (C) SheetJS https://sheetjs.com -->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="robots" content="noindex"/>
<title>ANOVA 统计分析工具</title>
<style>
  #drop {
    border: 2px dashed #bbb;
    border-radius: 5px;
    padding: 25px;
    text-align: center;
    font: 20pt bold "Vollkorn";
    color: #00AFBB;
    background-color: #ddd;
    margin: 10px;
  }
  .btn {
    display: inline-block;
    padding: 8px 16px;
    border-radius: 4px;
    border: 1px solid transparent;
    font-size: 14px;
    cursor: pointer;
  }
  .btn-primary {
    background-color: #007bff;
    color: white;
  }
  .btn-secondary {
    background-color: #6c757d;
    color: white;
  }
  .card {
    border: 1px solid #ddd;
    border-radius: 4px;
    margin: 10px;
    padding: 15px;
  }
  .row {
    display: flex;
    gap: 20px;
  }
  .col {
    flex: 1;
  }
</style>
<link rel="stylesheet" href="https://unpkg.com/x-data-spreadsheet/dist/xspreadsheet.css"/>
</head>
<body>
  <div id="drop">拖拽表格文件到这里</div>
  <div style="text-align:center; padding:10px">
    <input type="file" value="选择文件" name="xlfile" id="xlf" class="btn btn-secondary" />
    &rarr;
    <input type="button" value="ANOVA" onclick="do_stat()" id="stat" class="btn btn-primary" disabled/>
  </div>
  <div class="row">
    <div class="col card">
      <div class="card-title row">
        <div class="col"><span class="h5">输入数据</span></div>
        <div class="col" style="text-align: end;">
          <input type="submit" value="导出表格" id="xport" class="btn-outline-primary" onclick="export_data()"/>
        </div>        
      </div>      
      <div id="htmlout"></div>
    </div>
    <div class="col card">
      <div class="card-title row">
        <div class="col"><span class="h5">统计结果</span></div>
        <div class="col" style="text-align: end;">
          <input type="submit" value="导出表格" id="xport-stat" class="btn-outline-primary" onclick="export_stat()"/>
        </div>        
      </div>      
      <div id="statout"></div>
    </div>
  </div>

  <!-- 依赖库 -->
  <script src="https://cdn.jsdelivr.net/npm/danfojs@1.1.2/lib/bundle.min.js"></script>
  <script src="https://unpkg.com/x-data-spreadsheet/dist/xspreadsheet.js"></script>
  <script src="https://cdn.sheetjs.com/xlsx-latest/package/dist/shim.min.js"></script>
  <script src="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>
  <script src="https://cdn.sheetjs.com/xspreadsheet/xlsxspread.min.js"></script>
  <script src='https://cdn.jsdelivr.net/npm/jstat@1.9.6/dist/jstat.min.js'></script>

  <!-- 核心逻辑 -->
  <script>
    // -------------------- 初始化表格 --------------------
    var HTMLOUT = document.getElementById('htmlout');
    var xspr = x_spreadsheet(HTMLOUT, { showToolbar: false });
    var STATOUT = document.getElementById('statout');
    var xspr_stat = x_spreadsheet(STATOUT, { showToolbar: false });

    // -------------------- 导出函数定义 --------------------
    function export_data() {
      const new_wb = xtos(xspr.getData());
      XLSX.writeFile(new_wb, 'data-export.xlsx');
    }
    function export_stat() {
      const new_wb = xtos(xspr_stat.getData());
      XLSX.writeFile(new_wb, 'stat-export.xlsx');
    }

    // -------------------- 文件处理逻辑 --------------------
    document.getElementById('xlf').addEventListener('change', function(e) {
      const f = e.target.files[0];
      const reader = new FileReader();
      reader.onload = function(e) {
        const wb = XLSX.read(e.target.result, { type: 'array' });
        xspr.loadData(xtos(wb));
        document.getElementById('stat').disabled = false;
      };
      reader.readAsArrayBuffer(f);
    });

    // -------------------- 改进的ANOVA计算逻辑 --------------------
    function do_stat() {
      try {
        // 1. 获取有效数据
        const sheetData = xspr.getData()[Object.keys(xspr.getData())[0]];
        const validRows = Object.values(sheetData.rows).filter(row => 
          row.cells[0]?.text && row.cells[1]?.text && row.cells[2]?.text
        );

        // 2. 提取三组数值数据
        const groups = validRows.reduce((acc, row) => {
          acc.A.push(Number(row.cells[0].text));
          acc.B.push(Number(row.cells[1].text));
          acc.C.push(Number(row.cells[2].text));
          return acc;
        }, { A: [], B: [], C: [] });

        // 3. 验证数据有效性
        const totalCount = groups.A.length + groups.B.length + groups.C.length;
        if (totalCount < 3) throw new Error("至少需要3组有效数据");

        // 4. 计算基本统计量
        const calcStats = arr => ({
          n: arr.length,
          sum: jStat.sum(arr),
          mean: jStat.mean(arr),
          var: jStat.variance(arr)
        });

        const statsA = calcStats(groups.A);
        const statsB = calcStats(groups.B);
        const statsC = calcStats(groups.C);
        const totalData = [...groups.A, ...groups.B, ...groups.C];

        // 5. 计算平方和
        const SSW = (statsA.var*(statsA.n-1) + statsB.var*(statsB.n-1) + statsC.var*(statsC.n-1));
        const grandMean = jStat.mean(totalData);
        const SSB = statsA.n*(statsA.mean - grandMean)**2 + 
                   statsB.n*(statsB.mean - grandMean)**2 + 
                   statsC.n*(statsC.mean - grandMean)**2;

        // 6. 计算F值和临界值
        const df1 = 2;  // 组间自由度
        const df2 = totalData.length - 3;  // 组内自由度
        const F = (SSB/df1) / (SSW/df2);
        const pValue = 1 - jStat.ftest.cdf(F, df1, df2);
        const fCritical = jStat.ftest.inv(1 - 0.05, df1, df2);

        // 7. 构建结果表格
        const statData = [
          ["ANOVA 分析结果", "", "", "", "", "", ""],
          ["变异来源", "平方和(SS)", "自由度(df)", "均方(MS)", "F值", "P值", "F临界值"],
          ["组间变异", SSB.toFixed(2), df1, (SSB/df1).toFixed(2), F.toFixed(4), pValue.toExponential(3), fCritical.toFixed(4)],
          ["组内变异", SSW.toFixed(2), df2, (SSW/df2).toFixed(2), "", "", ""],
          ["总变异", (SSB+SSW).toFixed(2), df1+df2, "", "", "", ""]
        ];

        // 8. 更新统计结果表格
        xspr_stat.loadData({ 
          sheet1: { 
            rows: statData.reduce((acc, row, idx) => {
              acc[idx] = { cells: row.map(text => ({ text: text.toString() })) };
              return acc;
            }, {})
          }
        });

      } catch (error) {
        alert("分析错误: " + error.message);
        console.error(error);
      }
    }
  </script>
</body>
</html>